package com.fzy.core.system.service;

import ge.core.system.entity.Entity;
import ge.core.system.entity.Property;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;

import com.fzy.core.entity.Page;
import com.fzy.core.system.entity.PropertyWrapper;
import com.fzy.core.utils.DateUtils;
import com.fzy.core.utils.NumberUtils;
import com.fzy.core.utils.StringUtils;

public class OracleDbService extends AbstractDbService {
	
	public OracleDbService(String jndiName) {
		super(jndiName);
	}

	public OracleDbService(String driverClassName, String url, String username,
			String password) {
		super(driverClassName, url, username, password);
	}

	public Page getEntitiesFromTables(EntityQuery entityQuery, Page page) {
		String sql = "select a.table_name, b.comments from user_tables a, user_tab_comments b where a.table_name = b.table_name";
		List params = new LinkedList();
		String tableName = entityQuery.getTableName();
		if(!StringUtils.isBlank(tableName)){
			tableName = tableName.toUpperCase();
			sql += getConditionAntLike(tableName, "a.table_name", params);
			sql += getConditionAntLike(tableName, "b.table_name", params);
		}
		sql += getConditionSql(entityQuery.getComments(), "b.comments =", params);
		String orders = "";
		orders += getOrderSql("tableName", "a.table_name", page);
		sql += orders;
		return queryPage(sql, params.toArray(), new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Entity e = new Entity();
				e.setHasPrefix("0");
				e.setIsWork("1");
				e.setIsParentMapping("1");
				e.setPackageText("ge.app");

				String tableName = rs.getString("table_name").toLowerCase();
				String comment = escapeComment(rs.getString("comments"));
				e.setTableName(tableName);
				e.setCommentText(comment);
				handleCommentOfEntity(e, comment);

				e.setNameShow(getNameShow(tableName, comment));
				e.setNameText(tableName);

				return e;
			}
		}, page);
	}

	public Entity getEntityByTableName(String tableName) {
		if (StringUtils.isBlank(tableName))
			return new Entity();
		tableName = tableName.toUpperCase();
		Entity result = null;
		String sql = "select a.table_name, b.comments from user_tables a, user_tab_comments b where a.table_name = b.table_name";
		List params = new LinkedList();
		sql += getConditionSql(tableName, "a.table_name =", params);
		sql += getConditionSql(tableName, "b.table_name =", params);
		List l = query(sql, params.toArray(), new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Entity e = new Entity();
				e.setHasPrefix("0");
				e.setIsWork("1");
				e.setIsParentMapping("1");
				e.setPackageText("ge.app");

				String tableName = rs.getString("table_name").toLowerCase();
				String comment = escapeComment(rs.getString("comments"));
				e.setTableName(tableName);
				e.setCommentText(comment);
				handleCommentOfEntity(e, comment);

				e.setNameShow(getNameShow(tableName, comment));
				e.setNameText(tableName);

				return e;
			}
		});
		if (l.size() == 1)
			result = (Entity) l.get(0);
		return result;
	}

	public List getPropertiesByTableName(String tableName) {
		if (StringUtils.isBlank(tableName))
			return new LinkedList();
		tableName = tableName.toUpperCase();
		String sql = "";
		sql += " SELECT   a.table_name,";
		sql += "          a.column_name,";
		sql += "          b.comments,";
		sql += "          a.data_type,";
		sql += "          a.data_length,";
		sql += "          a.data_precision,";
		sql += "          a.data_scale,";
		sql += "          a.nullable,";
		sql += "          a.column_id,";
		sql += "          c.r_table_name,";
		sql += "          c.r_column_name,";
		sql += "          c.constraint_type";
		sql += "   FROM         user_tab_columns a";
		sql += "             LEFT JOIN";
		sql += "                user_col_comments b";
		sql += "             ON     a.table_name = b.table_name";
		sql += "                AND a.column_name = b.column_name";
		sql += "                AND b.table_name = ?";
		sql += "          LEFT JOIN";
		sql += "             (SELECT   e.table_name,";
		sql += "                       e.column_name,";
		sql += "                       f.table_name r_table_name,";
		sql += "                       f.column_name r_column_name,";
		sql += "                       d.constraint_type";
		sql += "                FROM         user_constraints d";
		sql += "                          LEFT JOIN";
		sql += "                             user_cons_columns e";
		sql += "                          ON d.constraint_name = e.constraint_name";
		sql += "                             AND e.table_name = ?";
		sql += "                       LEFT JOIN";
		sql += "                          user_cons_columns f";
		sql += "                       ON d.r_constraint_name = f.constraint_name";
		sql += "               WHERE       d.constraint_type IN ('R', 'P', 'U')";
		sql += "                       AND d.status = 'ENABLED'";
		sql += "                       AND d.table_name = ?) c";
		sql += "          ON a.table_name = c.table_name AND a.column_name = c.column_name";
		sql += "  WHERE   a.table_name = ?";
		List params = new LinkedList();
		params.add(tableName);
		params.add(tableName);
		params.add(tableName);
		params.add(tableName);
		return query(sql, params.toArray(), new RowMapper() {
			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				Property p = new Property();
				p.setIsQuery("0");
				p.setIsShow("1");
				p.setIsWork("1");

				int scale = NumberUtils.toInt(rs.getString("data_scale"), PropertyWrapper.SCALE_DEFAULT);
				String type = dbType2propertyType(
						rs.getString("data_type"), scale);
				String columnName = rs.getString("column_name").toLowerCase();
				String comment = escapeComment(rs.getString("comments"));
				String constraintType = rs.getString("constraint_type");
				boolean isKey = "P".equals(constraintType);
				boolean isRef = "R".equals(constraintType);
				boolean isUnique = "U".equals(constraintType);
				boolean isNotNull = "N".equals(rs.getString("nullable"));
				p.setColumnName(columnName);
				p.setCommentText(comment);
				handleCommentOfProperty(p, comment);
				p.setIsKey(isKey ? "1" : "0");
				p.setIsUnique(isUnique ? "1" : "0");
				p.setIsNotNull(isNotNull ? "1" : "0");
				p.setNameShow(getNameShow(columnName, comment));
				p.setNameText(StringUtils.underline2camel(columnName));
				p.setPropertyType(type);
				if (isRef) {
					p.setRefTable(rs.getString("r_table_name").toLowerCase());
					p.setRefColumn(rs.getString("r_column_name").toLowerCase());
				}
				if (PropertyWrapper.STRING.equals(type)) {
					p.setLengthValue(NumberUtils.createLong(rs
							.getString("data_length")));
				} else if (PropertyWrapper.NUMBER.equals(type)) {
					p.setPrecisionValue(NumberUtils.createLong(rs
							.getString("data_precision")));
					p.setScaleValue(NumberUtils.createLong(rs
							.getString("data_scale")));
				} else if (PropertyWrapper.DATE.equals(type)){
//					p.setDateFormatShow(DateUtils.PATTERN_SHOW);
				}
				return p;
			}
		});
	}
	
	private String dbType2propertyType(String dbType, int scale) {
		String result = "";
		if ("VARCHAR2".equalsIgnoreCase(dbType)) {
			result = PropertyWrapper.STRING;
		}
		if ("NUMBER".equalsIgnoreCase(dbType)) {
				result = PropertyWrapper.NUMBER;
				if(scale == 0)
					result = PropertyWrapper.LONG;
		}
		if ("DATE".equalsIgnoreCase(dbType)) {
			result = PropertyWrapper.DATE;
		}
		if ("CHAR".equalsIgnoreCase(dbType)) {
			result = PropertyWrapper.STRING;
		}
		return result;
	}

}
